﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using ChinaDynastyTours.Models;

namespace ChinaDynastyTours.Controls
{
    class VehicleOrderCtrl
    {
        #region 汽车预订信息获取

        /// <summary>
        /// 依据大行程计划号获取汽车预定数据
        /// </summary>
        /// <param name="tourGroupScheduleId">行程计划ID</param>
        /// <param name="expenseId">费用标识</param>
        /// <returns></returns>
        internal static DataTable GetVehicleOrderData(int tourGroupScheduleId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            //根据行程计划ID，取得‘费用表’与‘宾馆预定信息’表联合数据
            string sql = string.Format(@"SELECT v.[VehicleOrderID], v.[ExpenseID], v.[TourgroupID], v.[TourgroupName], v.[LocalPlanScheduleID], v.[ServiceDate],
                                                v.[ProviderID], v.[ProviderName], v.[GuestNum], v.[ArrivalFlight], v.[ArrivalTime], v.[LeaveFlight], 
                                                v.[LeaveTime], v.[Hotel], v.[VehicleNum], v.[CiceroniName],v.[ServiceName], v.[ContractPath],v.[SinglePrice], v.[Remark], v.[OrderState],
                                                e.[TypeBelong],e.[Status],e.[ProviderID],e.[ProviderName],e.[Budget],e.[ServiceName],e.[PayTotal],e.[Remark] as ExpenseRemark 
                                                FROM [fee_VehicleOrder] v,fee_Expense e WHERE  v.ExpenseID=e.ExpenseID AND e.TourGroupScheduleID={0}", tourGroupScheduleId);

            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        /// <summary>
        /// 根据团号及本地行程计划号获取汽车预订信息
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <param name="localPlanScheduleId"></param>
        /// <returns></returns>
        internal static DataTable GetVehicleOrderData(string tourGroupId, string localPlanScheduleId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;
            //根据行程计划ID，取得‘费用表’与‘预定信息’表联合数据
            string sql = string.Format(@"SELECT v.[VehicleOrderID], v.[ExpenseID], v.[TourgroupID], v.[TourgroupName], v.[LocalPlanScheduleID], v.[ServiceDate],
                                                v.[ProviderID], v.[ProviderName], v.[GuestNum], v.[ArrivalFlight], v.[ArrivalTime], v.[LeaveFlight], 
                                                v.[LeaveTime], v.[Hotel], v.[VehicleNum], v.[CiceroniName],v.[VehicleType],v.[ServiceName], v.[ContractPath],v.[SinglePrice], v.[Remark], v.[OrderState],
                                                e.[ProviderID], e.[ProviderName],e.[Budget],e.[Status],e.[PayStatus],e.[TypeBelong] 
                                                FROM fee_Expense e,fee_VehicleOrder v WHERE  v.TourGroupID='{0}' AND e.expenseid=v.expenseid AND v.LocalPlanScheduleID={1}",
                                                tourGroupId, localPlanScheduleId);

            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        #endregion

        #region 汽车预订信息的增、删、改

        internal static int CreateVehicleOrderAndExpenseData(ExpenseEntity expense, VehicleOrderEntity vehicleOrder)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sqlExpense = string.Format(@"INSERT INTO fee_Expense(
                                        [TourgroupID],
                                        [ProviderID],
                                        [ProviderName],
                                        [ServiceName],
                                        [Budget],
                                        [PayTotal],
                                        [ApplyPayTotal],
                                        [AdjustAmount],
                                        [Status],
                                        [TypeBelong],
                                        [ApplyPay],[PayStatus],[TourgroupName],[CreateByUserName],[TourGroupScheduleID],[CreateTime],[ExpenseTypeID],[ExpenseType])
                                        VALUES('{0}', {1}, '{2}', '{3}',{4},{5}, {6},{7}, '{8}', '{9}',{10},'{11}','{12}','{13}',{14},'{15}',{16},'{17}');Select SCOPE_IDENTITY();",
                                        expense.TourgroupID,
                                        expense.ProviderID,
                                        expense.ProviderName,
                                        expense.ServiceName,
                                        expense.Budget,
                                        expense.PayTotal,
                                        expense.ApplyPayTotal,
                                        expense.AdjustAmount,
                                        expense.Status,
                                        expense.TypeBelong,
                                        expense.ApplyPay,
                                        expense.PayStatus,
                                        expense.TourgroupName,
                                        expense.CreateByUserName,
                                        expense.TourGroupScheduleID,
                                        expense.CreateTime,
                                        expense.ExpenseTypeID,
                                        expense.ExpenseType);
            string expenseID = db.ExecuteScalar(CommandType.Text, sqlExpense).ToString();
            string sqlVehicleOrder = string.Format(@"INSERT INTO fee_VehicleOrder([ExpenseID], [TourgroupID], [TourgroupName], [LocalPlanScheduleID], 
                                                                            [ProviderID], [ProviderName], [GuestNum], [ArrivalFlight], [ArrivalTime], [LeaveFlight], 
                                                                            [LeaveTime], [Hotel], [VehicleNum], [SinglePrice], [CiceroniName], [ContractPath], [ServiceName], 
                                                                            [Remark], [OrderState],[ServiceDate],[VehicleType])
                                                                            VALUES({0},'{1}','{2}',{3},{4},'{5}',{6},'{7}','{8}','{9}','{10}','{11}',{12},{13},'{14}','{15}','{16}','{17}','{18}','{19}','{20}')",
                                                                           expenseID,
                                                                           expense.TourgroupID,
                                                                           vehicleOrder.TourgroupName,
                                                                           vehicleOrder.LocalPlanScheduleID,
                                                                           vehicleOrder.ProviderID,
                                                                           vehicleOrder.ProviderName,
                                                                           vehicleOrder.GuestNum,
                                                                           vehicleOrder.ArrivalFlight,
                                                                           vehicleOrder.ArrivalTime,
                                                                           vehicleOrder.LeaveFlight,
                                                                           vehicleOrder.LeaveTime,
                                                                           vehicleOrder.Hotel,
                                                                           vehicleOrder.VehicleNum,
                                                                           vehicleOrder.SinglePrice,
                                                                           vehicleOrder.CiceroniName,
                                                                           vehicleOrder.ContractPath,
                                                                           vehicleOrder.ServiceName,
                                                                           vehicleOrder.Remark,
                                                                           vehicleOrder.OrderState,
                                                                           vehicleOrder.ServiceDate,
                                                                           vehicleOrder.VehicleType);
            return db.ExecuteNonQuery(CommandType.Text, sqlVehicleOrder);
        }

        /// <summary>
        /// 计调更新汽车预订的部分数据信息，不包括()字段
        /// </summary>                                                                                                             
        /// <param name="expense"></param>                                                                                        
        /// <param name="flightOrder"></param>                                                                                     
        /// <returns></returns>                                                                                                    
        internal static int UpdateVehicleOrderData(ExpenseEntity expense, VehicleOrderEntity vehicleOrder)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlExpense = string.Format(@"UPDATE fee_Expense SET 
                                        [ProviderID]={0},
                                        [ProviderName]='{1}',
                                        [ServiceName]='{2}',
                                        [Budget]={3}
                                        WHERE ExpenseID={4}",
                                       expense.ProviderID,
                                       expense.ProviderName,
                                       expense.ServiceName,
                                       expense.Budget,
                                       expense.ExpenseID);

            db.ExecuteNonQuery(CommandType.Text, sqlExpense);
            string sqlVehicleOrder = string.Format(@"UPDATE fee_VehicleOrder SET [ExpenseID]={0}, [TourgroupID]='{1}', [TourgroupName]='{2}', [LocalPlanScheduleID]={3},
                                                                          [ProviderID]={4}, [ProviderName]='{5}',[GuestNum]={6},[ArrivalFlight]='{7}',[ArrivalTime]='{8}',
                                                                          [LeaveFlight]='{9}', [LeaveTime]='{10}', [Hotel]='{11}', [VehicleNum]={12}, [SinglePrice]={13},
                                                                          [CiceroniName]='{14}', [ContractPath]='{15}',[ServiceName]='{16}', [Remark]='{17}',[OrderState]='{18}',[ServiceDate]='{19}',[VehicleType]='{20}' WHERE VehicleOrderID={21}",
                                                                            vehicleOrder.ExpenseID,
                                                                            vehicleOrder.TourgroupID,
                                                                            vehicleOrder.TourgroupName,
                                                                            vehicleOrder.LocalPlanScheduleID,
                                                                            vehicleOrder.ProviderID,
                                                                            vehicleOrder.ProviderName,
                                                                            vehicleOrder.GuestNum,
                                                                            vehicleOrder.ArrivalFlight,
                                                                            vehicleOrder.ArrivalTime,
                                                                            vehicleOrder.LeaveFlight,
                                                                            vehicleOrder.LeaveTime,
                                                                            vehicleOrder.Hotel,
                                                                            vehicleOrder.VehicleNum,
                                                                            vehicleOrder.SinglePrice,
                                                                            vehicleOrder.CiceroniName,
                                                                            vehicleOrder.ContractPath,
                                                                            vehicleOrder.ServiceName,
                                                                            vehicleOrder.Remark,
                                                                            vehicleOrder.OrderState,
                                                                            vehicleOrder.ServiceDate,
                                                                            vehicleOrder.VehicleType,
                                                                            vehicleOrder.VehicleOrderID
                                                                            );

            return db.ExecuteNonQuery(CommandType.Text, sqlVehicleOrder);
        }
        /// <summary>
        /// 更改汽车的订单状态
        /// </summary>
        /// <param name="trainOrderId">订单号</param>
        /// <param name="orderState">状态</param>
        /// <returns></returns>
        internal static int UpdateVehicleOrderStateData(string vehicleOrderId, OrderStateEnum orderState)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE fee_VehicleOrder SET [OrderState]='{0}' WHERE VehicleOrderID={1}", orderState, vehicleOrderId);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        /// <summary>
        /// 更改汽车预订的的行程ID，因删除本地行程
        /// </summary>
        /// <param name="fromLocalPlanScheduleId">原行程ID</param>
        /// <param name="toLocalPlanScheduleId">新行程ID</param>
        /// <returns></returns>
        internal static int UpdateVehicleLocalScheduleID(string fromLocalPlanScheduleId, string toLocalPlanScheduleId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE fee_VehicleOrder SET [LocalPlanScheduleID]='{0}' WHERE LocalPlanScheduleID={1}", toLocalPlanScheduleId, fromLocalPlanScheduleId);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        /// <summary>
        ///  删除指定本地行程计划号的娱乐、餐单预订及费用信息
        /// </summary>
        /// <param name="localPlanScheduleId"></param>
        /// <returns></returns>
        internal static int DeleteVehicleOrder(string localPlanScheduleId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format("select expenseid from fee_VehicleOrder where LocalPlanScheduleID={0}", localPlanScheduleId);
            DbCommand dbCmd = db.GetSqlStringCommand(sql);

            object expenseObj = db.ExecuteScalar(dbCmd);

            if (expenseObj == null)
                return 0;

            sql = string.Format(@"DELETE FROM fee_VehicleOrder WHERE LocalPlanScheduleID={0};
                                         DELETE FROM fee_expense WHERE  expenseid={1};",//同时删除费用和地接社相关内容
                                         localPlanScheduleId,
                                         expenseObj.ToString());
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        
        /// <summary>
        /// 删除指定VehicleOrderID和费用标识ExpenseID的汽车预订及费用信息
        /// </summary>
        /// <param name="vehicleOrderID"></param>
        /// <param name="expenseId"></param>
        /// <returns></returns>
        internal static int DeleteVehicleOrder(string vehicleOrderID, string expenseId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"DELETE FROM fee_VehicleOrder WHERE VehicleOrderID={0};
                                         DELETE FROM fee_expense WHERE  expenseid={1};",//同时删除费用和地接社相关内容
                                         vehicleOrderID, expenseId);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        #endregion

        #region 汽车计调查询

        internal static DataTable GetVehicleOrderData(string tourGroupId, string providerId, DateTime startDate, DateTime endDate, OrderStateEnum orderState)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            //根据行程计划ID，取得‘费用表’与‘火车预定信息’表联合数据

            string szCondition = "";

            if (tourGroupId != "")
                szCondition = "v.TourGroupID='" + tourGroupId + "'";
            if (szCondition != "")
            {
                if (providerId != "")
                    szCondition += " AND e.ProviderID='" + providerId + "'";
            }
            else
            {
                if (providerId != "")
                    szCondition += " e.ProviderID='" + providerId + "'";
            }
            if (szCondition != "")
            {
                if (orderState.ToString()=="全部")
                    szCondition += string.Format(" AND v.ServiceDate>='{0}' AND v.ServiceDate<='{1}' AND v.expenseid=e.expenseid ", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"));
                else
                    szCondition += string.Format(" AND v.ServiceDate>='{0}' AND v.ServiceDate<='{1}' AND v.OrderState='{2}' AND v.expenseid=e.expenseid ", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), orderState);
            }
            else
            {
                if (orderState.ToString() == "全部")
                    szCondition += string.Format(" v.ServiceDate>='{0}' AND v.ServiceDate<='{1}' AND v.expenseid=e.expenseid ", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), orderState);
                else
                    szCondition += string.Format(" v.ServiceDate>='{0}' AND v.ServiceDate<='{1}' AND v.OrderState='{2}' AND v.expenseid=e.expenseid ", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), orderState);
            }



            string Sql = @"SELECT v.[VehicleOrderID], v.[ExpenseID], v.[TourgroupID], v.[TourgroupName], v.[LocalPlanScheduleID], v.[ServiceDate],
                                                v.[ProviderID], v.[ProviderName], v.[GuestNum], v.[ArrivalFlight], v.[ArrivalTime], v.[LeaveFlight], 
                                                v.[LeaveTime], v.[Hotel], v.[VehicleNum], v.[CiceroniName],v.[ServiceName], v.[VehicleType],v.[SinglePrice], v.[Remark], v.[OrderState],
                                                e.[ProviderID], e.[ProviderName],e.[Budget],e.[Status],e.[TypeBelong],e.[PayStatus],e.[ApplyPayTotal],e.[PayTotal],e.[Budget]-e.[ApplyPayTotal] as [ApplyPay]
                                                FROM fee_Expense e,fee_VehicleOrder v  ";

            if (szCondition != "")
                Sql += " WHERE " + szCondition;


            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        internal static DataTable GetVehicleOrderDataByExpenseId(string expenseId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string Sql = string.Format(@"SELECT v.[VehicleOrderID], v.[ExpenseID], v.[TourgroupID], v.[TourgroupName], v.[LocalPlanScheduleID], v.[ServiceDate],
                                                v.[ProviderID], v.[ProviderName], v.[GuestNum], v.[ArrivalFlight], v.[ArrivalTime], v.[LeaveFlight], 
                                                v.[LeaveTime], v.[Hotel], v.[VehicleNum], v.[CiceroniName],v.[ServiceName], v.[VehicleType],v.[SinglePrice], v.[Remark], v.[OrderState],
                                                e.[ProviderID], e.[ProviderName],e.[Budget],e.[Status],e.[TypeBelong] 
                                                FROM fee_Expense e,fee_VehicleOrder v  WHERE e.ExpenseID=v.ExpenseID AND e.ExpenseID={0} ", expenseId);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        #endregion
    }
}
